﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MAPDavy.Entities;
using System.Data.SqlClient;

namespace MAPDavy.Repository
{
    public class MatriculaRepository:MasterRepository,IMatriculaRepository
    {
        public List<MatriculaDTO> GetFromMatriculaByCriterioAndAnioAndSeccionId(string criterio, int seccionId, int anio)
        {
            var parmCriterio = new SqlParameter("@Criterio", criterio);
            var parmSeccion = new SqlParameter("@SeccionId", seccionId);
            var parmAnio = new SqlParameter("@Anio", anio.ToString());

            object[] param = new object[3];
            param[0] = parmCriterio;
            param[1] = parmSeccion;
            param[2] = parmAnio;


            var query = Context.Database.SqlQuery<MatriculaDTO>("GetFromMatriculaByCriterioAndAnioAndSeccionId @Criterio,@SeccionId,@anio", param).ToList();

            return query.ToList();
        }


        public Matricula GetFromMatriculaById(int id)
        {
            var matricula = from m in Context.Matriculas
                            .Include("Student")
                            .Include("Logs")
                            where m.Id == id
                            select m;

            return matricula.SingleOrDefault();
        }


        public void RegistrarMatricula(Matricula matricula)
        {
            var existe = (from m in Context.Matriculas
                            .Include("Student")
                            where m.Id == matricula.Id
                            select m).SingleOrDefault();

            var studentExiste = (from s in Context.Students
                                where s.Id == matricula.StudentId
                                select s).SingleOrDefault();

            try
            {
                if (existe == null)
                {
                    if (studentExiste == null)
                    {
                        Context.Students.Add(matricula.Student);
                        Context.SaveChanges(); 
                    }
                    else
                    {
                        studentExiste.Id = matricula.Student.Id;
                        studentExiste.DavyId = matricula.Student.DavyId;
                        studentExiste.FirtsName = matricula.Student.FirtsName;
                        studentExiste.LastName = matricula.Student.LastName;
                        studentExiste.BirtDate = matricula.Student.BirtDate;
                        studentExiste.Gender = matricula.Student.Gender;
                        studentExiste.FechaIngreso = matricula.Student.FechaIngreso;

                        if (matricula.Student.FilePhotoName != null)
                        {
                            studentExiste.FilePhotoName = matricula.Student.FilePhotoName;
                        }

                        Context.Entry(studentExiste).State = System.Data.EntityState.Modified;
                        Context.SaveChanges();
                    }

                    matricula.Student = null;

                    Context.Matriculas.Add(matricula);
                    Context.SaveChanges();
                }
                else
                {
                    existe.StudentId = matricula.StudentId;
                    existe.SeccionId = matricula.SeccionId;
                    existe.Activo = matricula.Activo;

                    if (matricula.FileNameReportCard != null)
                    {
                        existe.FileNameReportCard = matricula.FileNameReportCard;
                    }

                    existe.Student.Id = matricula.Student.Id;
                    existe.Student.DavyId = matricula.Student.DavyId;
                    existe.Student.FirtsName = matricula.Student.FirtsName;
                    existe.Student.LastName = matricula.Student.LastName;
                    existe.Student.BirtDate = matricula.Student.BirtDate;
                    existe.Student.Gender = matricula.Student.Gender;
                    existe.Student.FechaIngreso = matricula.Student.FechaIngreso;

                    if (matricula.Student.FilePhotoName != null)
                    {
                        existe.Student.FilePhotoName = matricula.Student.FilePhotoName;
                    }

                    Context.Entry(existe.Student).State = System.Data.EntityState.Modified;
                    Context.Entry(existe).State = System.Data.EntityState.Modified;
                    Context.SaveChanges();
                }
            }
            catch (Exception ex)
            {                
                throw new Exception(ex.InnerException.InnerException.Message);
            }

            
        }


        public List<Student> GetFromStudentByStudentId(string studentId, Int32 limit)
        {
            var query = from s in Context.Students
                        where s.Id.StartsWith(studentId)
                        orderby s.Id
                        select s;

            return query.Skip(0).Take(limit).ToList();
        }

        public List<Student> GetFromStudentByFirtsName(string firtsName, Int32 limit)
        {
            var query = from s in Context.Students
                        where s.FirtsName.StartsWith(firtsName)
                        orderby s.FirtsName
                        select s;

            return query.Skip(0).Take(limit).ToList();
        }

        public List<Student> GetFromStudentByLastName(string lastName, Int32 limit)
        {
            var query = from s in Context.Students
                        where s.LastName.StartsWith(lastName)
                        orderby s.LastName
                        select s;

            return query.Skip(0).Take(limit).ToList();
        }


        public Matricula GetFromMatriculaByAnioAndStudentId(string anio, string studentId)
        {
            var query = from m in Context.Matriculas
                        where m.Anio == anio && m.StudentId == studentId
                        select m;

            return query.SingleOrDefault();
        }


        public void DeleteMatricula(int id)
        {
            var existe = (from m in Context.Matriculas
                          where m.Id == id
                          select m).SingleOrDefault();
            
            if (existe !=null)
            {
                Context.Matriculas.Remove(existe);
                Context.SaveChanges();
            }
        }


        public bool CanDelete(int id)
        {
            bool can = true;

            var existe = (from m in Context.Matriculas
                          .Include("Logs")
                          .Include("Scores")
                          where m.Id == id
                          select m).SingleOrDefault();

            if (existe != null)
            {
                if (existe.Logs.Count>0 || existe.Scores.Count > 0)
                {
                    can = false;
                }
            }
            else
            {
                can = false;
            }

            return can;
        }


        public int AddMatricula(Matricula matricula)
        {
            Context.Matriculas.Add(matricula);
            Context.SaveChanges();

            return 1;
        }
    }
}
